package com.bridgeintelligent.tag.webserver.dim.mgmt.service.report;

import cn.hutool.json.JSONUtil;
import com.bridgeintelligent.tag.webserver.dim.mgmt.api.report.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import static javax.servlet.http.HttpServletResponse.SC_OK;

@Api(tags = "运营分析EXCEL导出")
@AllArgsConstructor
@RestController
@Slf4j
@RequestMapping(value = "/api/reportExport")
public class ReportExportController {

    private ReportService  reportService;

    @ApiOperation(value = "系统整体使用概况导出-1:近半年系统整体明细;2:近半年客群整体明细3:近半年标签整体明细", notes = "系统整体使用概况-1:近半年系统整体明细;2:近半年客群整体明细3:近半年标签整体明细")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true),
            @ApiImplicitParam(paramType = "query", name = "type", value = "查询类型", required = true)
    })
    @PostMapping("/systemUse")
    public void systemUse(String sortWord,String type, HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            List<ReportSystemUseInfo> infos = reportService.systemUseInfo(sortWord);
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            //创建sheet数据
            createSystemSheet(wb, commonStyle ,infos,type);

            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                String fileName ;
                if (StringUtils.equals("1",type)){
                    fileName = "近半年系统整体明细-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                }else if (StringUtils.equals("2",type)){
                    fileName = "近半年客群整体明细-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                }else{
                    fileName = "近半年标签整体明细-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                }
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年系统整体使用概况导出异常", e);
                throw new RuntimeException("近半年系统整体使用概况导出异常");
            }
        }catch (Exception e){
            log.error("近半年系统整体使用概况导出异常!", e);
            throw new RuntimeException("近半年系统整体使用概况导出异常!");
        }

    }

    //========================系统整体使用概况处理=====================================================================================================================
    public void createSystemSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<ReportSystemUseInfo> infos,String type) {
        try {
            // 创建sheet
            Sheet sheet ;
            List<String> titles;
            if (StringUtils.equals("1",type)){
                sheet = wb.createSheet("近半年系统整体明细");
                titles = Arrays.asList("月份","系统用户数","月活用户百分比","登陆访问量");
            }else if (StringUtils.equals("2",type)){
                sheet = wb.createSheet("近半年客群整体明细");
                titles = Arrays.asList("月份","新建客群量","客群总量","有效客群量","无效客群量","客群导出次数");
            }else{
                sheet = wb.createSheet("近半年标签整体明细");
                titles = Arrays.asList("月份","新建基础标签量","基础标签总量",
                        "新建定制标签量","定制标签总量","有效定制标签总量","无效定制标签总量",
                        "新建组合标签量","组合标签总量","有效组合标签量","无效组合标签量",
                        "被使用基础标签个数","被使用基础标签占比","基础标签被使用次数","被使用标签个数","被使用标签占比","被使用标签次数");
            }
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            for (ReportSystemUseInfo info : infos) {
                Row row = sheet.createRow(rowNum++);
                //获取rowData数据--因为数据量小直接一个方法判断rowData了
                List<String> rowData = getSystenUseRowData(info,m,type);
                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    public List<String> getSystenUseRowData(ReportSystemUseInfo info,int length,String type) {
        //计算每行需要的数据长度
        List<String> rowData = new ArrayList<>(length + 1);
        if (StringUtils.equals("1",type)){
            rowData.add(info.getDatetime());
            rowData.add(info.getUserNum()+"");
            rowData.add(info.getActivePrecent()+"%");
            rowData.add(info.getLoginNum()+"");
        }else if (StringUtils.equals("2",type)){
            rowData.add(info.getDatetime());
            rowData.add(info.getCustomerNum()+"");
            rowData.add(info.getCustomerAllTotal()+"");
            rowData.add(info.getCustomerTotal()+"");
            rowData.add(info.getCustomerExpireNum()+"");
            rowData.add(info.getExportNum()+"");
        }else{
            rowData.add(info.getDatetime());
            rowData.add(info.getBaseNum()+"");
            rowData.add(info.getBaseTotal()+"");
            rowData.add(info.getDingzhiNum()+"");
            rowData.add(info.getDingzhiAllTotal()+"");
            rowData.add(info.getDingzhiTotal()+"");
            rowData.add(info.getDingzhiExpireNum()+"");
            rowData.add(info.getCombinationNum()+"");
            rowData.add(info.getCombinationAllTotal()+"");
            rowData.add(info.getCombinationTotal()+"");
            rowData.add(info.getCombinationExpireNum()+"");
            rowData.add(info.getBaseDimNum()+"");
            rowData.add(info.getBaseDimPrecent()+"%");
            rowData.add(info.getBaseNum()+"");
            rowData.add(info.getDimNum()+"");
            rowData.add(info.getDimPrecent()+"%");
            rowData.add(info.getUseNum()+"");
        }
        return rowData;
    }

    //---------------------------------------------------------------------------------------------------------

    @ApiOperation(value = "机构整体使用概况导出-1:近半年机构整体使用概况;2:近半年机构客群使用概况3:近半年机构标签使用概况", notes = "机构整体使用概况-1:近半年机构整体使用情况;2:近半年机构客群使用情况3:近半年机构标签使用情况")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true),
            @ApiImplicitParam(paramType = "query", name = "startTime", value = "开始时间", required = true),
            @ApiImplicitParam(paramType = "query", name = "endTime", value = "结束时间", required = true),
            @ApiImplicitParam(paramType = "query", name = "orgId", value = "机构编号", required = false),
            @ApiImplicitParam(paramType = "query", name = "type", value = "查询类型", required = true)
    })
    @PostMapping("/orgUse")
    public void orgUse(String sortWord,String orgId, String type, String startTime,String endTime,HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            List<ReportOrgUseInfo> infos = reportService.orgUseInfoWithTotal(sortWord,orgId,startTime,endTime);
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            //创建sheet数据
            createOrgSheet(wb, commonStyle ,infos,type);

            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                String fileName ;
                if (StringUtils.equals("1",type)){
                    fileName = "近半年机构整体使用概况-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                }else if (StringUtils.equals("2",type)){
                    fileName = "近半年机构客群使用概况-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                }else{
                    fileName = "近半年机构标签使用概况-"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                }
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年机构使用概况导出异常", e);
                throw new RuntimeException("近半年机构使用概况导出异常");
            }
        }catch (Exception e){
            log.error("近半年机构使用概况导出异常!", e);
            throw new RuntimeException("近半年机构使用概况导出异常!");
        }

    }

    //========================机构整体使用概况处理=====================================================================================================================
    public void createOrgSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<ReportOrgUseInfo> infos,String type) {
        try {
            // 创建sheet
            Sheet sheet ;
            List<String> titles;
            if (StringUtils.equals("1",type)){
                sheet = wb.createSheet("近半年机构整体使用概况");
                titles = Arrays.asList("排名","所属机构","登陆访问量","系统用户数","月活百分比");
            }else if (StringUtils.equals("2",type)){
                sheet = wb.createSheet("近半年机构客群使用概况");
                titles = Arrays.asList("排名","所属机构","客群总量","新建客群量","有效客群量","客群导出次数");
            }else{
                sheet = wb.createSheet("近半年机构标签使用概况");
                titles = Arrays.asList("排名","所属机构",
                        "新建标签总量","新建定制标签量","有效定制标签量","新建组合标签量","有效组合标签量",
                        "使用基础标签个数","使用基础标签占比","使用基础标签次数","使用标签个数","使用标签占比","使用标签次数");
            }
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            for (ReportOrgUseInfo info : infos) {
                Row row = sheet.createRow(rowNum++);
                //获取rowData数据--因为数据量小直接一个方法判断rowData了
                List<String> rowData = getOrgUseRowData(info,m,type);
                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    public List<String> getOrgUseRowData(ReportOrgUseInfo info,int length,String type) {
        //计算每行需要的数据长度
        List<String> rowData = new ArrayList<>(length + 1);
        if (StringUtils.equals("1",type)){
            rowData.add(info.getOrderNum());
            rowData.add(info.getOrgName());
            rowData.add(info.getLoginNum()+"");
            rowData.add(info.getUserNum()+"");
            rowData.add(info.getActivePrecent()+"%");
        }else if (StringUtils.equals("2",type)){
            rowData.add(info.getOrderNum());
            rowData.add(info.getOrgName());
            rowData.add(info.getCustomerNum()+"");
            rowData.add(info.getCustomerTotal()+"");
            rowData.add(info.getExportNum()+"");
        }else{
            rowData.add(info.getOrderNum());
            rowData.add(info.getOrgName());
            rowData.add(info.getCreateDimTotal()+"");
            rowData.add(info.getDingzhiNum()+"");
            rowData.add(info.getDingzhiTotal()+"");
            rowData.add(info.getCombinationNum()+"");
            rowData.add(info.getCombinationTotal()+"");
            rowData.add(info.getBaseDimNum()+"");
            rowData.add(info.getBaseDimPrecent()+"%");
            rowData.add(info.getBaseUseNum()+"");
            rowData.add(info.getDimNum()+"");
            rowData.add(info.getDimPrecent()+"%");
            rowData.add(info.getUseNum()+"");
        }
        return rowData;
    }



    //----------------------------------------------------------------------------------------------------------

    @ApiOperation(value = "近半年某机构用户访问明细导出", notes = "近半年某机构用户访问明细导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true),
            @ApiImplicitParam(paramType = "query", name = "orgId", value = "机构编号", required = false),
            @ApiImplicitParam(paramType = "query", name = "orgName", value = "机构名称", required = false),
            @ApiImplicitParam(paramType = "query", name = "startTime", value = "开始时间", required = true),
            @ApiImplicitParam(paramType = "query", name = "endTime", value = "结束时间", required = true)
    })
    @PostMapping("/orgUserLogin")
    public void orgUserLogin(String sortWord,String orgId, String orgName,String startTime,String endTime, HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            ReportPage<OrgUserLoginModel> pageInfo = reportService.orgUserLogin(orgId,sortWord,startTime,endTime,1,Integer.MAX_VALUE);
            List<OrgUserLoginModel> infos = pageInfo.getContent() == null ? new ArrayList<>() : pageInfo.getContent();
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            String fileName = "近半年"+orgName+"用户访问明细";
            //创建sheet数据
            createOrgUserLoginSheet(wb, commonStyle ,infos,fileName);

            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                fileName = fileName + DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年某机构用户访问明细导出异常", e);
                throw new RuntimeException("近半年某机构用户访问明细导出异常");
            }
        }catch (Exception e){
            log.error("近半年某机构用户访问明细导出异常!", e);
            throw new RuntimeException("近半年某机构用户访问明细导出异常!");
        }

    }

    public void createOrgUserLoginSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<OrgUserLoginModel> infos,String fileName) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet(fileName);
            List<String> titles = Arrays.asList("排名","用户姓名","登陆访问量");
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (OrgUserLoginModel info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                rowData.add(info.getOrderNum()+"");
                rowData.add(info.getUserName());
                rowData.add(info.getLoginNum()+"");

                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

//-----------------------------------------------------------------------------------------

    @ApiOperation(value = "近半年某机构用户客群使用明细导出", notes = "近半年某机构用户客群使用明细导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true),
            @ApiImplicitParam(paramType = "query", name = "orgId", value = "机构编号", required = false),
            @ApiImplicitParam(paramType = "query", name = "orgName", value = "机构名称", required = false),
            @ApiImplicitParam(paramType = "query", name = "startTime", value = "开始时间", required = true),
            @ApiImplicitParam(paramType = "query", name = "endTime", value = "结束时间", required = true)
    })
    @PostMapping("/orgUserCustomer")
    public void orgUserCustomer(String sortWord,String orgId, String orgName,String startTime,String endTime, HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            ReportPage<OrgUserCustomerModel> pageInfo = reportService.orgUserCustomer(orgId,sortWord,startTime,endTime,1,Integer.MAX_VALUE);
            List<OrgUserCustomerModel> infos = pageInfo.getContent() == null ? new ArrayList<>() : pageInfo.getContent();
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            String fileName = "近半年"+orgName+"客群使用明细";
            //创建sheet数据
            createOrgUserCustomerSheet(wb, commonStyle ,infos,fileName);
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                fileName = fileName + DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年某机构用户客群使用明细导出异常", e);
                throw new RuntimeException("近半年某机构用户客群使用明细导出异常");
            }
        }catch (Exception e){
            log.error("近半年某机构用户客群使用明细导出异常!", e);
            throw new RuntimeException("近半年某机构用户客群使用明细导出异常!");
        }

    }

    public void createOrgUserCustomerSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<OrgUserCustomerModel> infos,String fileName) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet(fileName);
            List<String> titles = Arrays.asList("排名","用户姓名","新建客群量","客群导出次数");
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (OrgUserCustomerModel info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                rowData.add(info.getOrderNum()+"");
                rowData.add(info.getUserName());
                rowData.add(info.getCustomerNum()+"");
                rowData.add(info.getExportNum()+"");

                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }


    //------------------------------------------------------------------------------------------

    @ApiOperation(value = "近半年某机构用户标签使用明细导出", notes = "近半年某机构用户标签使用明细导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true),
            @ApiImplicitParam(paramType = "query", name = "orgId", value = "机构编号", required = false),
            @ApiImplicitParam(paramType = "query", name = "orgName", value = "机构名称", required = false),
            @ApiImplicitParam(paramType = "query", name = "startTime", value = "开始时间", required = true),
            @ApiImplicitParam(paramType = "query", name = "endTime", value = "结束时间", required = true)
    })
    @PostMapping("/orgUserDim")
    public void orgUserDim(String sortWord,String orgId, String orgName,String startTime,String endTime, HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            ReportPage<OrgUserDimModel> pageInfo = reportService.orgUserDim(orgId,sortWord,startTime,endTime,1,Integer.MAX_VALUE);
            List<OrgUserDimModel> infos = pageInfo.getContent() == null ? new ArrayList<>() : pageInfo.getContent();
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            String fileName = "近半年"+orgName+"标签使用明细";
            //创建sheet数据
            createOrgUserDimSheet(wb, commonStyle ,infos,fileName);
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                fileName = fileName + DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年某机构用户标签使用明细导出异常", e);
                throw new RuntimeException("近半年某机构用户标签使用明细导出异常");
            }
        }catch (Exception e){
            log.error("近半年某机构用户标签使用明细导出异常!", e);
            throw new RuntimeException("近半年某机构用户标签使用明细导出异常!");
        }

    }

    public void createOrgUserDimSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<OrgUserDimModel> infos,String fileName) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet(fileName);
            List<String> titles = Arrays.asList("排名","用户姓名","新建定制标签量","新建组合标签量","使用标签个数","使用标签次数");
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (OrgUserDimModel info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                rowData.add(info.getOrderNum()+"");
                rowData.add(info.getUserName());
                rowData.add(info.getDingzhiNum()+"");
                rowData.add(info.getCombinationNum()+"");
                rowData.add(info.getDimNum()+"");
                rowData.add(info.getUseNum()+"");

                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    //--------------------------------------------------------------------------------------

    @ApiOperation(value = "01组合标签个人创建情况02定制标签个人创建情况03标签个人使用情况导出", notes = "01组合标签个人创建情况定制标签个人创建情况标签个人使用情况导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true),
            @ApiImplicitParam(paramType = "query", name = "selectType", value = "查询类型", required = false)
    })
    @PostMapping("/combinationCustomizeInfo")
    public void combinationCustomizeInfo(String sortWord,String selectType,HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            ReportPage<ReportCombinationCustomizeInfo> pageInfo = reportService.combinationBustomizeInfo(selectType,sortWord,1,Integer.MAX_VALUE);
            List<ReportCombinationCustomizeInfo> infos = pageInfo.getContent() == null ? new ArrayList<>() : pageInfo.getContent();
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            String fileName;
            if (StringUtils.equals(selectType,"00")){
                fileName = "近半年标签个人创建概况";
            }else if (StringUtils.equals(selectType,"01")){
                fileName = "近半年组合标签个人创建概况";
            }else if (StringUtils.equals(selectType,"02")){
                fileName = "近半年定制标签个人创建概况";
            }else{
                fileName = "近半年标签个人使用概况";
            }
            //创建sheet数据
            createComCustInfoSheet(wb, commonStyle ,infos,fileName,selectType);
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                fileName = fileName + DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年标签创建和使用概况导出异常", e);
                throw new RuntimeException("近半年标签创建和使用概况导出异常");
            }
        }catch (Exception e){
            log.error("近半年标签创建和使用概况导出异常!", e);
            throw new RuntimeException("近半年标签创建和使用概况导出异常!");
        }

    }

    public void createComCustInfoSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<ReportCombinationCustomizeInfo> infos,String fileName,String selectType) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet(fileName);
            List<String> titles;
            if (StringUtils.equals(selectType,"03")){
                titles = Arrays.asList("排名","标签使用人","所属机构","使用标签个数","使用标签次数");
            }else{
                titles = Arrays.asList("排名","标签创建人","所属机构","标签类型","创建标签数量","创建标签被使用次数");
            }
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (ReportCombinationCustomizeInfo info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                if (StringUtils.equals(selectType,"03")){
                    rowData.add(info.getOrderNum()+"");
                    rowData.add(info.getUserName());
                    rowData.add(info.getOrgName());
                    rowData.add(info.getDimNum()+"");
                    rowData.add(info.getUseNum()+"");
                }else{
                    rowData.add(info.getOrderNum()+"");
                    rowData.add(info.getUserName());
                    rowData.add(info.getOrgName());
                    rowData.add(info.getDimType());
                    rowData.add(info.getDimNum()+"");
                    rowData.add(info.getUseNum()+"");
                }
                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    //-----------------------------------------------------------------------------------------------------

    @ApiOperation(value = "近半年客群使用明细导出", notes = "近半年客群使用明细导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true)
    })
    @PostMapping("/customerUseInfo")
    public void customerUseInfo(String sortWord,HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            ReportPage<ReportCustomerUseInfo> pageInfo = reportService.customerUseInfo(sortWord,1,Integer.MAX_VALUE);
            List<ReportCustomerUseInfo> infos = pageInfo.getContent() == null ? new ArrayList<>() : pageInfo.getContent();
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            //创建sheet数据
            createCustomerUseInfoSheet(wb, commonStyle ,infos);
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                String fileName = "近半年客群使用明细" + DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年客群使用明细导出异常", e);
                throw new RuntimeException("近半年客群使用明细导出异常");
            }
        }catch (Exception e){
            log.error("近半年客群使用明细导出异常!", e);
            throw new RuntimeException("近半年客群使用明细导出异常!");
        }

    }

    public void createCustomerUseInfoSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<ReportCustomerUseInfo> infos) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet("近半年客群使用明细");
            List<String> titles = Arrays.asList("排名","客群名称","客群创建人","所属机构","客群导出次数");
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (ReportCustomerUseInfo info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                rowData.add(info.getOrderNum()+"");
                rowData.add(info.getCustomerName());
                rowData.add(info.getUserName());
                rowData.add(info.getOrgName());
                rowData.add(info.getExportNum()+"");

                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    //-------------------------------------------------------------------------------------------

    @ApiOperation(value = "近半年个人客群创建明细导出", notes = "近半年个人客群创建明细导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true)
    })
    @PostMapping("/customerCreateInfo")
    public void customerCreateInfo(String sortWord,HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            ReportPage<ReportCustomerCreateInfo> pageInfo = reportService.customerCreateInfo(sortWord,1,Integer.MAX_VALUE);
            List<ReportCustomerCreateInfo> infos = pageInfo.getContent() == null ? new ArrayList<>() : pageInfo.getContent();
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            //创建sheet数据
            createCustomerCreateInfoSheet(wb, commonStyle ,infos);
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                String fileName = "近半年个人客群创建明细" + DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年个人客群创建明细导出异常", e);
                throw new RuntimeException("近半年个人客群创建明细导出异常");
            }
        }catch (Exception e){
            log.error("近半年个人客群创建明细导出异常!", e);
            throw new RuntimeException("近半年个人客群创建明细导出异常!");
        }

    }

    public void createCustomerCreateInfoSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<ReportCustomerCreateInfo> infos) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet("近半年个人客群创建明细");
            List<String> titles = Arrays.asList("排名","客群创建人","所属机构","创建客群数","创建客群导出次数");
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (ReportCustomerCreateInfo info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                rowData.add(info.getOrderNum()+"");
                rowData.add(info.getUserName());
                rowData.add(info.getOrgName());
                rowData.add(info.getCreateNum()+"");
                rowData.add(info.getExportNum()+"");

                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    //--------------------------------------------------------------------------------------

    @ApiOperation(value = "近半年客户画像系统功能模块机构使用明细导出", notes = "近半年客户画像系统功能模块机构使用明细导出")
    @ApiImplicitParams({
            @ApiImplicitParam(paramType = "query", name = "modelType", value = "模块类型", required = true),
            @ApiImplicitParam(paramType = "query", name = "modelName", value = "模块名称", required = true),
            @ApiImplicitParam(paramType = "query", name = "sortWord", value = "排序字段", required = true)
    })
    @PostMapping("/systemModelDetailInfo")
    public void systemModelDetailInfo(String modelType,String modelName,String sortWord,HttpServletRequest request, HttpServletResponse response) throws IOException {
        try{
            List<ReportSystemModelDetailInfo> infos = reportService.modelDetailInfo(modelType,sortWord);
            log.info("返回数据结果："+ JSONUtil.toJsonStr(infos));
            // 创建工作薄对象
            XSSFWorkbook wb=new XSSFWorkbook();
            CellStyle commonStyle = setStyle(wb);
            //创建sheet数据
            createModelDetailInfoSheet(wb, commonStyle ,infos,modelName);
            // 创建文档信息
            try(OutputStream os=response.getOutputStream()) {
                response.reset();
                response.setStatus(SC_OK);
                String fileName = "近半年客户画像"+modelName+"模块机构使用明细"+ DateFormatUtils.format(new Date(),"yyyy-MM-dd");
                fileName = new String(fileName.getBytes(),"UTF-8");
                response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s.xlsx\"", fileName));
                response.setContentType("application/msexcel");
                wb.write(os);
                os.flush();
            } catch (IOException e) {
                log.error("近半年客户画像系统功能模块机构使用明细导出异常", e);
                throw new RuntimeException("近半年客户画像系统功能模块机构使用明细导出异常");
            }
        }catch (Exception e){
            log.error("近半年客户画像系统功能模块机构使用明细导出异常!", e);
            throw new RuntimeException("近半年客户画像系统功能模块机构使用明细导出异常!");
        }

    }

    public void createModelDetailInfoSheet(XSSFWorkbook wb, CellStyle commonStyle ,List<ReportSystemModelDetailInfo> infos,String modelName) {
        try {
            // 创建sheet
            Sheet sheet = wb.createSheet("近半年客户画像系统"+modelName+"模块机构使用明细");
            List<String> titles = Arrays.asList("排名","机构名称","使用人数","使用次数","人均使用次数");
            int m = titles.size();
            int rowNum = 0; // 行号
            //设置列宽
            for (int i = 0; i < m + 2; i++) {
                sheet.setColumnWidth(i, 3000);
            }
            //第1行
            Row r0 = sheet.createRow(rowNum++);
            r0.setHeight((short) 700);
            for (int i = 0; i < m; i++) {
                Cell tempCell = r0.createCell(i);
                tempCell.setCellValue(titles.get(i));
                tempCell.setCellStyle(commonStyle);
            }
            //循环填充数据
            List<String> rowData;
            for (ReportSystemModelDetailInfo info : infos) {
                Row row = sheet.createRow(rowNum++);
                //计算每行需要的数据长度
                rowData = new ArrayList<>(m + 1);
                rowData.add(info.getOrderNum()+"");
                rowData.add(info.getOrgName());
                rowData.add(info.getUserNum()+"");
                rowData.add(info.getUseNum()+"");
                rowData.add(info.getPerNum()+"");

                for (int i = 0; i < rowData.size(); i++) {
                    //列表数据
                    Cell tempCell2 = row.createCell(i);
                    tempCell2.setCellValue(rowData.get(i));
                    tempCell2.setCellStyle(commonStyle);
                }
                rowData = null;
            }
        } catch (Exception e) {
            log.error("导出报表数据出现异常", e);
        }
    }

    public CellStyle setStyle(XSSFWorkbook wb) {
        //表头字体
        Font headerFont = wb.createFont();
        headerFont.setFontName("宋体");
        headerFont.setFontHeightInPoints((short) 18);
        headerFont.setBold(true);
        headerFont.setColor(Font.COLOR_NORMAL);
        //正文字体
        Font contextFont = wb.createFont();
        contextFont.setFontName("宋体");
        contextFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        //表头样式，左右上下居中
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setFont(headerFont);
        // 左右居中
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        // 上下居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setLocked(true);
        // 自动换行
        headerStyle.setWrapText(false);
        //单元格样式，左右上下居中 边框
        CellStyle commonStyle = wb.createCellStyle();
        commonStyle.setFont(contextFont);
        // 左右居中
        commonStyle.setAlignment(HorizontalAlignment.CENTER);
        // 上下居中
        commonStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        commonStyle.setLocked(true);
        // 自动换行
        commonStyle.setWrapText(false);
        //单元格样式，左右上下居中 边框
        CellStyle commonWrapStyle = wb.createCellStyle();
        commonWrapStyle.setFont(contextFont);
        //单元格样式，竖向 边框
        CellStyle verticalStyle = wb.createCellStyle();
        verticalStyle.setFont(contextFont);
        CellStyle commonStyleNoBorder = wb.createCellStyle();
        commonStyleNoBorder.setFont(contextFont);
        commonStyleNoBorder.setLocked(true);
        // 自动换行
        commonStyleNoBorder.setWrapText(false);
        CellStyle alignLeftStyle = wb.createCellStyle();
        alignLeftStyle.setFont(contextFont);
        alignLeftStyle.setLocked(true);
        // 自动换行
        alignLeftStyle.setWrapText(false);
        //单元格样式，左对齐 无边框
        CellStyle alignLeftNoBorderStyle = wb.createCellStyle();
        alignLeftNoBorderStyle.setFont(contextFont);
        alignLeftNoBorderStyle.setLocked(true);
        // 自动换行
        alignLeftNoBorderStyle.setWrapText(false);
        //单元格样式，右对齐
        CellStyle alignRightStyle = wb.createCellStyle();
        alignRightStyle.setFont(contextFont);
        alignRightStyle.setLocked(true);
        // 自动换行
        alignRightStyle.setWrapText(false);
        return commonStyle;
    }


    /**
     * sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
     *             if(n > 1){
     *                 sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, n));
     *             }
     *             int k = 1;
     *             if (n >1 ){ //一个单元格合并会有问题
     *                 for (int i = 0; i < m - 1; i++) {
     *                     k = k + n;
     *                     sheet.addMergedRegion(new CellRangeAddress(0, 0, k, k + n - 1));
     *                 }
     *             }
     * @param wb
     * @param commonStyle
     * @param excelModel
     */
}

